USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_6]    Script Date: 11/23/2011 15:17:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_6]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_6]
GO

USE [GESTION_MORA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_6]    Script Date: 11/23/2011 15:17:23 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WASP_D0007_CARGO_MORAEMP_MORA_SUC_6]
AS

-- GESION COMERCIAL DESAFILIADOS
TRUNCATE TABLE  DBO.GMComercialDesafil

INSERT INTO DBO.GMComercialDesafil
SELECT     
	 GMBASEGESTCOMER.Cuenta_Empresa 
	,GMBASEGESTCOMER.Nombre_Empresa
	,GMBASEGESTCOMER.Estado_Empresa
	,GMBASEGESTCOMER.Condicion_Empresa
	,GMBASEGESTCOMER.Clasificacion_Empresa 
	,GMBASEGESTCOMER.SucOper 
	,GMBASEGESTCOMER.Agencia 
	,GMBASEGESTCOMER.GERENTE 
	,GMBASEGESTCOMER.RC 
	,GMBASEGESTCOMER.RG 
	,GMBASEGESTCOMER.C_MANT 
	,GMBASEGESTCOMER.CI_MANT	
	,GMBASEGESTCOMER.EJECUTIVO_EMPRESAS
	,GMBASEGESTCOMER.SEGMENTO 
	,GMBASEGESTCOMER.CR 
	,GMBASEGESTCOMER.ESTADO 
	,GMBASEGESTCOMER.CONTRATO 
	,GMBASEGESTCOMER.Cuenta 
	,GMBASEGESTCOMER.Operacion 
	,GMBASEGESTCOMER.Nombre_Deudor 
	,Cuenta_Aval1 
	,Nombre_Aval1 
	,Cuenta_Aval2 
	,Nombre_Aval2 
	,MM60 
	,DP 
	,DR 
	,GMHISTORICOSALDO.Clasificacion 
	,Vcto_Impago = convert(varchar,(GMBASEGESTCOMER.Vcto_Impago),105) 
	,GMBASEGESTCOMER.Valor_Cuota
FROM		
	GMBASEGESTCOMER Left Join GMHISTORICOSALDO ON
		GMBASEGESTCOMER.Operacion = GMHISTORICOSALDO.Operacion AND 
  	GMBASEGESTCOMER.Cuenta    = GMHISTORICOSALDO.Cuenta
--	GMBASEGESTCOMER,
--	GMHISTORICOSALDO 
WHERE
--	GMBASEGESTCOMER.Operacion *= GMHISTORICOSALDO.Operacion AND 
--	GMBASEGESTCOMER.Cuenta *= GMHISTORICOSALDO.Cuenta AND
	GMBASEGESTCOMER.Condicion_Empresa = 'EMPRESA ADHERIDA' AND 
	GMBASEGESTCOMER.Estado_Empresa = 'DESAFILIADO' AND 
	GMHISTORICOSALDO.Clasificacion = 'ME - Analizada' AND 
	GMBASEGESTCOMER.Valor_Cuota >= 1000 AND
	GMHISTORICOSALDO.Fecha = cast(convert(varchar ,getdate(),112) as datetime)

GROUP BY	
	GMBASEGESTCOMER.Cuenta_Empresa, GMBASEGESTCOMER.Nombre_Empresa, GMBASEGESTCOMER.Estado_Empresa, GMBASEGESTCOMER.Condicion_Empresa, 
	GMBASEGESTCOMER.Clasificacion_Empresa, 	GMBASEGESTCOMER.SucOper, GMBASEGESTCOMER.Agencia, GMBASEGESTCOMER.GERENTE,  GMBASEGESTCOMER.RC, 
	GMBASEGESTCOMER.RG, GMBASEGESTCOMER.C_MANT, GMBASEGESTCOMER.CI_MANT, GMBASEGESTCOMER.EJECUTIVO_EMPRESAS, GMBASEGESTCOMER.SEGMENTO, 
	GMBASEGESTCOMER.CR, GMBASEGESTCOMER.ESTADO, GMBASEGESTCOMER.CONTRATO, GMBASEGESTCOMER.Cuenta, GMBASEGESTCOMER.Operacion, 
	GMBASEGESTCOMER.Nombre_Deudor, GMBASEGESTCOMER.Cuenta_Aval1, 	GMBASEGESTCOMER.Nombre_Aval1, 	GMBASEGESTCOMER.Cuenta_Aval2, 
	GMBASEGESTCOMER.Nombre_Aval2, 	GMBASEGESTCOMER.MM60, GMBASEGESTCOMER.DP, GMBASEGESTCOMER.DR, GMHISTORICOSALDO.Clasificacion, 
	GMBASEGESTCOMER.Vcto_Impago, GMBASEGESTCOMER.Valor_Cuota
GO

